<?php

namespace App\Exports;

use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Events\BeforeSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;

class EmailExport implements FromCollection, WithColumnWidths, WithEvents, WithHeadings
{
    protected $data;

    public function __construct(Collection $data)
    {
        // 验证和清理数据
        $this->data = $data->map(function($item) {
            if (!is_array($item)) {
                return [];
            }
            
            // 清理每个字段的数据
            return array_map(function($value) {
                if (is_null($value)) {
                    return '';
                }
                if (is_array($value)) {
                    return implode("\n", array_filter($value, function($v) {
                        return !is_null($v) && trim((string)$v) !== '';
                    }));
                }
                // 清理特殊字符，但保留换行符
                $cleaned = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]/', '', (string)$value);
                return trim($cleaned);
            }, $item);
        });

    }

    public function collection()
    {
        // 直接返回清理后的数据，不添加额外的空行和标题
        return $this->data;
    }

    public function columnWidths(): array
    {
        // 设置每列的宽度
        return [
            'A' => 17,
            'B' => 17,
            'C' => 17,
            'D' => 17,
            'E' => 14,
            'F' => 14,
            'G' => 30,
            'H' => 14,
            'I' => 23,
            'L' => 23,
        ];
    }

    public function headings(): array {
        return [];
//        return [
//            '提单号',
//            '分单号',
//            '目的地',
//            '件数',
//            '重量(kg)',
//            '包装',
//            '入仓号',
//            '体积(m³)',
//            '是否交电',
//            '交货日期',
//            '交货车牌',
//            '备注',
//        ];
    }


    public function registerEvents(): array
    {
        return [
            BeforeSheet::class => function(BeforeSheet $event) {
                // 合并单元格
                $event->sheet->getDelegate()->mergeCells('A1:L1'); // 合并第一行 A 到 L
                $event->sheet->getDelegate()->setCellValue('A1', 'EVERY MOMENT LOGISTICS CO.,LTD');

                $event->sheet->getDelegate()->getStyle('A1:L1')->getFill()->setFillType(Fill::FILL_SOLID);
                $event->sheet->getDelegate()->getStyle('A1:L1')->getFill()->getStartColor()->setARGB('FFDAE1F0');

                $event->sheet->getDelegate()->mergeCells('A2:D2'); // 合并第二行 A 到 D
                $event->sheet->getDelegate()->setCellValue('A2', '目的地');

                $event->sheet->getDelegate()->mergeCells('E2:F2'); // 合并第二行 E 到 F
                $event->sheet->getDelegate()->setCellValue('E2', '航班信息');

                $event->sheet->getDelegate()->mergeCells('G2:H2'); // 合并第二行 G 到 H
                $event->sheet->getDelegate()->setCellValue('G2', '起飞时间');

                $event->sheet->getDelegate()->setCellValue('I2', '板型');

                $event->sheet->getDelegate()->mergeCells('J2:L2'); // 合并第二行 J 到 K
                $event->sheet->getDelegate()->setCellValue('J2', 'PIVOT');

//                $event->sheet->getDelegate()->setCellValue('L2', '交货时间');

                // 安全获取第一行数据
                $data = $this->data->toArray();
                $firstRowData = isset($data[0]) && is_array($data[0]) ? $data[0] : [];
                
                // 保持第三行与第二行一致
                $event->sheet->getDelegate()->mergeCells('A3:D3'); // 合并第三行 A 到 D
                $event->sheet->getDelegate()->setCellValue('A3', $firstRowData['des'] ?? '');

                $event->sheet->getDelegate()->mergeCells('E3:F3'); // 合并第三行 E 到 F
                $event->sheet->getDelegate()->setCellValue('E3', $firstRowData['flight_number'] ?? '');

                $event->sheet->getDelegate()->mergeCells('G3:H3'); // 合并第三行 G 到 H
                $event->sheet->getDelegate()->setCellValue('G3', $firstRowData['take_off_time'] ?? '');

                $event->sheet->getDelegate()->setCellValue('I3', $firstRowData['bans'] ?? '');

                $event->sheet->getDelegate()->mergeCells('J3:L3'); // 合并第三行 J 到 K
                $event->sheet->getDelegate()->setCellValue('J3', $firstRowData['pivot'] ?? '');

//                $event->sheet->getDelegate()->setCellValue('L3', $firstRowData['delivery_time'] ?? '');

                // 设置居中对齐
                $alignment = new Alignment();
                $alignment->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $alignment->setVertical(Alignment::VERTICAL_CENTER);

                // 应用居中对齐到所有合并的单元格
                $cellRanges = [
                    'A1:L1', 'A2:D2', 'E2:F2', 'G2:H2', 'I2', 'I3', 
                    'J2:L2', 'L2', 'L3', 'A3:D3', 'E3:F3', 'G3:H3', 'J3:L3'
                ];
                
                foreach ($cellRanges as $range) {
                    $event->sheet->getDelegate()->getStyle($range)->getAlignment()->setHorizontal($alignment->getHorizontal());
                    $event->sheet->getDelegate()->getStyle($range)->getAlignment()->setVertical($alignment->getVertical());
                }

                // 从数据中移除第一行（航班信息），只保留提单信息
                if (count($data) > 0) {
                    unset($data[0]);
                    $this->data = collect(array_values($data));
                }

                $sheet = $event->sheet->getDelegate();
                $sheet->getRowDimension(1)->setRowHeight(25);
                $sheet->getRowDimension(2)->setRowHeight(25);
                $sheet->getRowDimension(3)->setRowHeight(25);


                $head = ['提单号', '分单号', '目的地', '件数', '重量(kg)', '包装', '入仓号', '体积(m³)', '是否交电', '交货日期', '交货车牌', '备注'];
                $row = 4;
                foreach ($head as $index => $title){
                    $sheet->setCellValue($this->getColumnName($index) . $row, $title);
                }
                $row++;

                foreach ($data as $index => $item){
                    $item = array_values($item);
                    foreach ($item as $key => $value){
                        $sheet->setCellValue($this->getColumnName($key) . $row, $value);
                    }
                    $sheet->getStyle('G'.$row)
                        ->getAlignment()
                        ->setWrapText(true);
//                    $sheet->getRowDimension($row)->setRowHeight(-1); // 自动调整行高
                    $row++;
                }
                $data = [];

                $this->data = collect($data);

            },
        ];
    }

    private function getColumnName($columnIndex) {
        $columnName = '';

        while ($columnIndex >= 0) {
            $columnName = chr($columnIndex % 26 + ord('A')) . $columnName;
            $columnIndex = intval($columnIndex / 26) - 1;
        }

        return $columnName;
    }


}